package com.itqn.dao.Impl;

import cn.hutool.core.util.StrUtil;
import com.itqn.dao.ProductDao;
import com.itqn.pojo.Category;
import com.itqn.pojo.Product;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class ProductDaoImpl implements ProductDao {

    @Override
    public Integer totalCount(String name, Integer categoryId, Integer status) throws Exception {
        BaseDao baseDao = new BaseDao();
        //编写sql语句
        StringBuilder sb = new StringBuilder();
        sb.append("select count(id) from product ");
        Integer flag = 0;
        boolean isBlank = StrUtil.isNotBlank(name);
        if (isBlank || categoryId != null || status != null) {
            sb.append("where ");
        }
        if (isBlank) {
            flag = 1;
            sb.append("`name` LIKE CONCAT('%',?,'%') ");
        }
        if (categoryId != null) {
            if (isBlank) {
                flag = 2;
                sb.append("and category_id=? ");
            } else {
                flag = 3;
                sb.append("category_id=? ");
            }
        }
        if (status != null) {
            if (isBlank && categoryId != null) {
                flag = 6;
                sb.append("and `status`=? ");
            } else if (categoryId != null) {
                flag = 5;
                sb.append("and `status`=? ");
            } else if (isBlank) {
                flag = 4;
                sb.append("and `status`=? ");
            } else {
                flag = 7;
                sb.append("`status`=? ");
            }
        }

        ResultSet set = null;
        switch (flag) {
            case 1:
                set = baseDao.getlist(sb.toString(), new Object[]{name});
                break;
            case 2:
                set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId});
                break;
            case 3:
                set = baseDao.getlist(sb.toString(), new Object[]{categoryId});

                break;
            case 4:
                set = baseDao.getlist(sb.toString(), new Object[]{name, status});

                break;
            case 5:
                set = baseDao.getlist(sb.toString(), new Object[]{categoryId, status});
                break;
            case 6:
                set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId, status});
                break;
            case 7:
                set = baseDao.getlist(sb.toString(), new Object[]{status});
                break;
            default:
                set = baseDao.getlist(sb.toString(), new Object[]{});
                break;
        }

        int number = 0;
        if (set.next()) {
            number = set.getInt(1);
        }
        // number是总条数
        return number;
    }

    @Override
    public Product findById(int id) throws Exception {
        //创建一个实体类对象
        Product product = null;
        BaseDao baseDao = new BaseDao();
        String sql = "SELECT product.id,product.category_id,product.`name`,product.image,category.`name`,product.price,product.description FROM product INNER JOIN category ON product.category_id=category.id WHERE product.id=?";
        ResultSet set = baseDao.getlist(sql, new Object[]{id});
        if (set.next()) {
            product = new Product();
            product.setId(set.getLong("product.id"));
            product.setName(set.getString("product.name"));
            product.setImage(set.getString("product.image"));
            Category category = new Category();
            category.setName(set.getString("category.name"));
            product.setCategory(category);
            product.setCategoryId(set.getLong("product.category_id"));
            product.setPrice(set.getBigDecimal("product.price"));
            product.setDescription(set.getString("product.description"));
        }
        return product;
    }

    @Override
    public int addProduct(Product product) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "insert into product (name, category_id, price, image, description) values (?,?,?,?,?)";
        result = baseDao.update(sql, new Object[]{product.getName(), product.getCategoryId(), product.getPrice(), product.getImage(), product.getDescription()});
        return result;
    }

    @Override
    public int deleteGoods(int id) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "DELETE FROM product WHERE product.id=?";
        result = baseDao.update(sql, new Object[]{id});
        return result;
    }

    @Override
    public int updateGoods(Product product) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "UPDATE product SET name=?, category_id=?, price=?, image=?, description=? WHERE product.id=?";
        result = baseDao.update(sql, new Object[]{product.getName(), product.getCategoryId(), product.getPrice(), product.getImage(), product.getDescription(), product.getId()});
        return result;
    }

    @Override
    public int deleteAll(String key) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String[] n1 = key.split(",");
        String sqls = "";
        Object[] a = new Object[n1.length];

        for (int i = 0; i < n1.length; i++) {
            a[i] = n1[i];
            if (i == n1.length - 1) {
                sqls += "?";
            } else {
                sqls += "?" + ",";
            }

        }
        String sql = "DELETE FROM product WHERE id in(" + sqls + ")";
        result = baseDao.update(sql, a);
        return result;
    }

    @Override
    public List<Product> query(String name, Integer categoryId, Integer status, Integer pageSize, Integer page) throws Exception {
        List<Product> list = new ArrayList<>();
        //创建一个实体类对象
        Product product = null;
        int flag = 0;
        BaseDao baseDao = new BaseDao();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT product.id,product.`name`,category_id,price,image,description,product.status,category.`name` ");
        sb.append("FROM product ");
        sb.append("INNER JOIN category ");
        sb.append("ON product.category_id=category.id ");
        boolean isBlank = StrUtil.isNotBlank(name);
        if (isBlank || categoryId != null || status != null) {
            sb.append("where ");
        }
        if (isBlank) {
            flag = 1;
            sb.append("product.`name` LIKE CONCAT('%',?,'%') ");
        }
        if (categoryId != null) {
            if (isBlank) {
                flag = 2;
                sb.append("and category_id=? ");
            } else {
                flag = 3;
                sb.append("category_id=? ");
            }
        }
        if (status != null) {
            if (isBlank && categoryId != null) {
                flag = 6;
                sb.append("and product.`status`=? ");
            } else if (categoryId != null) {
                flag = 5;
                sb.append("and product.`status`=? ");
            } else if (isBlank) {
                flag = 4;
                sb.append("and product.`status`=? ");
            } else {
                flag = 7;
                sb.append("product.`status`=? ");
            }
        }
        Boolean isPage = page != null && pageSize != null;
        if (isPage) {
            sb.append("limit ?, ?");
        }
        ResultSet set = null;
        switch (flag) {
            case 1:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{name});
                }
                break;
            case 2:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId});
                }
                break;
            case 3:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{categoryId, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{categoryId});
                }
                break;
            case 4:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, status, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, status});
                }
                break;
            case 5:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{categoryId, status, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{categoryId, status});
                }
                break;
            case 6:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId, status, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{name, categoryId, status});
                }
                break;
            case 7:
                if (isPage) {
                    set = baseDao.getlist(sb.toString(), new Object[]{status, (page - 1) * pageSize, pageSize});
                } else {
                    set = baseDao.getlist(sb.toString(), new Object[]{status});
                }
                break;
            default:
                set = baseDao.getlist(sb.toString(), new Object[]{(page - 1) * pageSize, pageSize});
                break;
        }

        if (set == null) {
            return Collections.emptyList();
        }
        while (set.next()) {
            product = new Product();
            product.setId(set.getLong("product.id"));
            product.setName(set.getString("product.name"));
            product.setCategoryId(set.getLong("category_id"));
            product.setImage(set.getString("product.image"));
            Category category = new Category();
            category.setName(set.getString("category.name"));
            product.setCategory(category);
            product.setPrice(set.getBigDecimal("product.price"));
            product.setStatus(set.getInt("product.status"));
            list.add(product);
        }
        return list;
    }

    @Override
    public Integer count(Long categoryId) throws Exception {
        BaseDao baseDao = new BaseDao();
        String sql = "select count(*) as num from product where category_id = ?";
        ResultSet set = baseDao.getlist(sql, new Object[]{categoryId});
        set.next();
        return set.getInt("num");
    }

    @Override
    public void updatestatic(Product product) throws Exception {
        BaseDao baseDao = new BaseDao();
        String sql = "UPDATE product set `status`=? where id=?";
        baseDao.update(sql, new Object[]{product.getStatus(), product.getId()});
    }


}
